package com.cpi.cornpp.futurepurchase.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.futurepurchase.dto.CustomerFuturePurchaseDTO;
import com.cpi.cornpp.futurepurchase.exception.FuturePurchaseException;
import com.cs.common.pool.DBDAO;

public class CustomerFuturePurchaseDAO extends DBDAO {
	private static final String SQL_SELECT_CUSTOMER_FUTURE_PURCHASES = "select 	cfd.customer_id,c.description,periodApp.short_name + ' ' + convert( varchar, periodApp.year ) applicationPeriod,periodAcc.short_name + ' ' + convert( varchar, periodAcc.year ) accountPeriod, cfd.future_type_id, cfd.quantity cantidad_cliente, cf.reference, cf.quantity cantidad_futuro, cf.unit_price, cfd.corn_future_distribution_id, cf.corn_future_id from corn_futures_distributions cfd left join vw_customers c on cfd.customer_id = c.customer_id left join items_distributions cfi on cfd.corn_future_distribution_id = cfi.corn_future_distribution_id left join vw_items it on cfi.item_id = it.item_id and ( it.item_id like ? or it.description like ? ) inner join corn_futures cf on cfd.corn_future_id = cf.corn_future_id inner join futures_periods periodApp on periodApp.future_period_id = cf.application_future_period_id inner join futures_periods periodAcc on periodAcc.future_period_id = cf.account_future_period_id where ( c.customer_id like ? or c.description like ? ) and account_future_period_id in (select future_period_id from futures_periods where year >= ? and year <= ?)";
	
	public List getCustomerFuturePurchasesByCriteria(String product, String customer, int beginPeriodYear, int endPeriodYear, int orderField, boolean ascendingOrder)throws FuturePurchaseException{
		List futurePurchases = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		
		try{
			futurePurchases = new ArrayList(0);
			if ((beginPeriodYear != 0) && (endPeriodYear != 0)){
				sql = new StringBuffer(SQL_SELECT_CUSTOMER_FUTURE_PURCHASES);
				sql.append(SQL_ORDER_BY);
				
				switch (orderField){
					case 0: 
						sql.append("cfd.");
						sql.append(CustomerFuturePurchaseDTO.CUSTOMER_ID);
					break;
					case 1:
						sql.append("c.");
						sql.append(CustomerFuturePurchaseDTO.CUSTOMER_DESCRIPTION);
					break;
				}
				
				if (!ascendingOrder){
					sql.append(SQL_ASCENDING_ORDER);
				} else {
					sql.append(SQL_DESCENDING_ORDER);
				}
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setString(1, "%" + product + "%");
				pstm.setString(2, "%" + product + "%");
				pstm.setString(3, "%" + customer + "%");
				pstm.setString(4, "%" + customer + "%");
				pstm.setInt(5, beginPeriodYear);
				pstm.setInt(6, endPeriodYear);
				
				rs = executeQuery(pstm);
				
				futurePurchases = mapRows(rs);
			}
			
		} catch(SQLException sqle){
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			sqle.printStackTrace(System.out);
			throw futurePurchaseException;
		} catch (Exception exception){
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(exception, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			exception.printStackTrace(System.out);
			throw futurePurchaseException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return futurePurchases;
	}
	
	private CustomerFuturePurchaseDTO mapRow(ResultSet rs, int index) throws SQLException{
		CustomerFuturePurchaseDTO dto = null;
		
		if ((rs != null) && (rs.absolute(index))){
			dto = new CustomerFuturePurchaseDTO();
			
			dto.setCustomerId(rs.getString(CustomerFuturePurchaseDTO.CUSTOMER_ID));
			dto.setCustomerDescription(rs.getString(CustomerFuturePurchaseDTO.CUSTOMER_DESCRIPTION));
			dto.setFutureType(rs.getString(CustomerFuturePurchaseDTO.FUTURE_TYPE));
			dto.setCustomerQuantity(new Integer(rs.getInt(CustomerFuturePurchaseDTO.CUSTOMER_QUANTITY)));
			dto.setFuturePurchaseAccountPeriod(rs.getString(CustomerFuturePurchaseDTO.FUTURE_PURCHASE_ACCOUNT_PERIOD));
			dto.setFuturePurchaseApplicationPeriod(rs.getString(CustomerFuturePurchaseDTO.FUTURE_PURCHASE_APPLICATION_PERIOD));
			dto.setFuturePurchaseQuantity(new Integer(rs.getInt(CustomerFuturePurchaseDTO.FUTURE_PURCHASE_QUANTITY)));
			dto.setFuturePurchaseUnitPrice(new Double(rs.getDouble(CustomerFuturePurchaseDTO.FUTURE_PURCHASE_UNIT_PRICE)));
			dto.setCornFutureDistributionId(new Integer(rs.getInt(CustomerFuturePurchaseDTO.FUTURE_DISTRIBUTION_ID)));
			dto.setFuturePurchaseId(new Integer(rs.getInt(CustomerFuturePurchaseDTO.FUTURE_PURCHASE_ID)));
		}
		
		return dto;
	}
	
	private List mapRows(ResultSet rs) throws SQLException{
		List customerFuturePurchases = null;
		
		customerFuturePurchases = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				customerFuturePurchases.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return customerFuturePurchases;
	}
}